How do you put an elephant on a containership in 3 steps?

PGConfEu Berlin 2022
2022-10-27

Who am I

• Lætitia Avrot
• PostgreSQL recognized contributor
• PostgreSQL Europe board member
• #PostgresWomen co-founder
• EDB Field CTO
• @l_avrot
• mydbanotebook.org / psql-tips.org

Postgres on containers

• What?
• Why?
• How?
• Is this for me?

What

Containers are isolated from one another and bundle their own software, libraries and configuration files; they can communicate with each other through well-defined channels. Because all of the containers share the services of a single operating system kernel, they use fewer resources than virtual machines.

What

• isolated
• light weight
• secured communications
• encapsulation of all softwares, configuration and librairies

Several technologies

• Docker
• LXC (LinuX Containers)
• Podman
• ...

is that all?

• No
• It goes deeper
• Here comes... Kubernetes!

Cloud Native PG

• Postgres Kubernetes operator
• Apache 2.0 license
• In the CNCF sanbox

Kubernetes

Kubernetes is an open-source container orchestration system for automating software deployment, scaling, and management.

Why?

• Who has standalone Postgres nowadays?
• Who manages less than 5 clusters in production?

➜ More things to do, less administrators

DBA Tasks

• Installations/Configurations
• Securization
• Backup policy implementation
• HA policy implementation
• Minor/Major upgrades
• Monitoring
• Troubleshooting

Changing scripting into configuration

• installation/configurations
• securization
• Backup/HA implementation
• Minor upgrades

And more!

• Self-healing
• Everything in one place
• Service encapsulation
• Self-service for devs

ACID

• Atomicity
• Consistency
• Isolation
• Durability

Atomicity

Atomicity is a property that ensures that a database follows the all or nothing rule. In other words, the database considers all transaction operations as one whole unit or atom.

Consistency

Consistency is a property ensuring that only valid data following all rules and constraints is written in the database. When a transaction results in invalid data, the database reverts to its previous state, which abides by all customary rules and constraints.

Isolation

Isolation is a property that guarantees the individuality of each transaction, and prevents them from being affected from other transactions. It ensures that transactions are securely and independently processed at the same time without interference

Durability

Durability is a property that enforces completed transactions, guaranteeing that once each one of them has been committed, it will remain in the system even in case of subsequent failures.

ACID

• Atomicity ✅
• Consistency ✅
• Isolation ✅
• Durability ❌

Stateless/statefull

The way containers are designed, and particularly the way Docker is designed, the assumption is that the container is stateless.

Some tried early...

... And failed

It shouldn't be a reason not to try again!

The key change

Kubernetes 1.14 and persistent volumes

September 2021

70% of 500 executives and technology leaders are running stateful workloads in production with databases topping the list.

How?

• Declare your configuration
• Deploy
• Relax

Simple

# Example of PostgreSQL cluster
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example
spec:
  instances: 3

  primaryUpdateStrategy: unsupervised
  storage:
    size: 1Gi

Specific Postgres version

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
   # [...]
spec:
   # [...]
   imageName: ghcr.io/cloudnative-pg/postgresql:13.6
   #[...]

Backups

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
# [...]
spec:
  backup:
    barmanObjectStore:
      destinationPath: "[destination path here]"
      s3Credentials:
        accessKeyId:
          name: aws-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: aws-creds
          key: ACCESS_SECRET_KEY

Settings

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
# [...]
spec:
  postgresql:
    parameters:
      shared_buffers: "1GB"
      auto_explain.log_min_duration: "10s"
    pg_hba:
      - host laetitia laetitia all scram-sha-256
  # [...]

Resources

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
# [...]
  resources:
    requests:
      memory: "32Mi"
      cpu: "50m"
    limits:
      memory: "128Mi"
      cpu: "100m"
  # [...]

Monitoring

apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: cluster-example
spec:
  selector:
    matchLabels:
      postgresql: cluster-example
  podMetricsEndpoints:
  - port: metrics

Monitoring

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example
# [...]
spec:
# [...]
  monitoring:
    customQueriesConfigMap:
      - name: example-monitoring
        key: custom-queries

Migrating

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-monolith
spec:
  bootstrap:
    initdb:
      import:
        type: monolith
        databases:
          - banking
        roles:
          - bank_user
        source:
          externalCluster: cluster-pg96
...

Migrating

...
  externalClusters:
    - name: cluster-pg96
      connectionParameters:
        # Use the correct IP or host name for the source database
        host: pg96.local
        user: postgres
        dbname: postgres
        sslmode: require
      password:
        name: cluster-pg96-superuser
        key: password

Rolling upgrades

Triggered when...
• Changing the imageName
• Changing PostgreSQL configuration that needs a restart
• A change on resources
• A change on PVC on AKS
• after an operator update

Rolling upgrades

2 modes
• Automated primaryUpdateStrategy:unsupervised
• Manual primaryUpdateStrategy:supervised

Logs

• CSV Format (on disk)
• JSON format with kubectl logs

Is that for you?

It depends...

• Is your team experimented with Kubernetes?
• Is your team experimented with Postgres?
• Do you manage more than 5 Postgres clusters?

Caution

Your Postgres database still needs

• Data modeling
• Correct SQL queries
• Careful monitoring

Any questions?